package com.offcn;

import com.until.JDBCUntils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class EMPManger {
    private static Scanner sc=new Scanner(System.in);
        public static void login(){
            System.out.println("登录");
            System.out.println("请输入用户名");
            String username = sc.nextLine();
            System.out.println("请输入密码");
            String password = sc.nextLine();


            Connection conn=null;
            PreparedStatement stmt=null;
            ResultSet rs=null;
            try {
                conn = JDBCUntils.getConnection();
                stmt = conn.prepareStatement("select * from user  where username=? and password=?");
                stmt.setString(1,username);
                stmt.setString(2,password);
                 rs = stmt.executeQuery();
                 if (rs.next()){
                     System.out.println("登录成功");
                     index();
                 }else {
                     System.out.println("用户名或密码有误");
                     login();
                 }

            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUntils.CloesAll(rs,stmt,conn);
            }

        }
        public static void index(){
            System.out.println("-----欢迎来到图书管理系统----");
            System.out.println("1.查询所有图书");
            System.out.println("2.根据编号查询");
            System.out.println("3.添加新书");
            System.out.println("4.修改图书信息");
            System.out.println("5.删除图书");
            System.out.println("0.退出系统");


            System.out.println("请输入编号：");
            String num = sc.nextLine();

            switch (num){
                case "1":selectAllEmp();index();break;
                case "2":selectEmpById();index();break;
                case "3":addEmp();index();break;
                case "4":updateEmp();index();break;
                case "5":deleteEmpById();index();break;
                default:
                    System.out.println("欢迎下次登录");
                    break;


            }

        }
        // 1）查询图书
        public static void selectAllEmp(){
            //获取连接
       Connection conn=null;
       PreparedStatement stmt=null;
       ResultSet rs=null;
            try {
                //获取连接
                 conn = JDBCUntils.getConnection();
                 //获取预编译通道
                 stmt = conn.prepareStatement("select * from book1 ");
                 //执行SQL语句
                 rs = stmt.executeQuery();
                 //执行结果
                System.out.println("id\tname\tprice\twriter\tpress\tdate\tcount");
                while (rs.next()){
                    System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"+rs.getString(6)+"\t"+rs.getString(7));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUntils.CloesAll(rs,stmt,conn);
            }

        }
        //2)根据编号查询
        public static void selectEmpById(){
            PreparedStatement stmt=null;
            Connection conn=null;
            ResultSet rs=null;

            try {
                //创建连接
                 conn = JDBCUntils.getConnection();
                //获取预编译通道
                 stmt = conn.prepareStatement("select * from book1 where id=?");
                System.out.println("请输入图书编号");
                int i= Integer.parseInt(sc.nextLine());
                //给？赋值
                stmt.setInt(1,i);
                //执行SQL语句
                 rs = stmt.executeQuery();
                System.out.println("id\tname\tprice\twriter\tpress\tdate\tcount");
                 while (rs.next()){
                     System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"+rs.getString(6)+"\t"+rs.getString(7));

                 }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUntils.CloesAll(rs,stmt,conn);
            }

        }
        //3）添加新书
        public static void addEmp(){
            PreparedStatement stmt=null;
            //创建连接
            Connection conn = null;
            //获取预编译通道
            try {
                conn = JDBCUntils.getConnection();
                stmt = conn.prepareStatement("insert into book1(id,name,price,writer,press,date,count) " + "values (?,?,?,?,?,?,?)");
                System.out.println("请输入图书编号");
                int eid = Integer.parseInt(sc.nextLine());
                System.out.println("请输入书名称");
                String ename = sc.nextLine();
                System.out.println("请输价格");
                int eprice = Integer.parseInt(sc.nextLine());
                System.out.println("作者名称");
                String zname = sc.nextLine();
                System.out.println("出版社");
                String where = sc.nextLine();
                System.out.println("出版日期");
                String etime = sc.nextLine();
                System.out.println("数量");
                int enumb = Integer.parseInt(sc.nextLine());
                //？赋值
                stmt.setInt(1,eid);
                stmt.setString(2,ename);
                stmt.setInt(3,eprice);
                stmt.setString(4,zname);
                stmt.setString(5,where);
                stmt.setString(6,etime);
                stmt.setInt(7,enumb);

                //执行SQL语句
                int num = stmt.executeUpdate();
                if (num>0){
                    System.out.println("添加成功");
                }else {
                    System.out.println("添加失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUntils.CloesAll(null,stmt,conn);
            }

        }
        //4)修改图书
    public static void updateEmp(){
            //调用查找员工的方法
            selectEmpById();
        PreparedStatement stmt=null;
        Connection conn=null;

        try {
            //创建连接
             conn = JDBCUntils.getConnection();
            //获取编译预通道
             stmt = conn.prepareStatement("update book1 set name=?,price=?,writer=?,press=?,date=?,count=? where id=?");
             //

            System.out.println("请输入书名称");
            String ename = sc.nextLine();
            System.out.println("请输价格");
            int eprice = Integer.parseInt(sc.nextLine());
            System.out.println("作者名称");
            String zname = sc.nextLine();
            System.out.println("出版社");
            String where = sc.nextLine();
            System.out.println("出版日期");
            String etime = sc.nextLine();
            System.out.println("数量");
            int enumb = Integer.parseInt(sc.nextLine());
            System.out.println("请输入图书编号");
            int eid = Integer.parseInt(sc.nextLine());
            //给？赋值

            stmt.setString(1,ename);
            stmt.setInt(2,eprice);
            stmt.setString(3,zname);
            stmt.setString(4,where);
            stmt.setString(5,etime);
            stmt.setInt(6,enumb);
            stmt.setInt(7,eid);
            //执行SQL语句
            int num = stmt.executeUpdate();
            if (num>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUntils.CloesAll(null,stmt,conn);
        }
    }
        //5)删除图书
    public static void deleteEmpById(){
        Connection conn=null;
        PreparedStatement stmt=null;
 
        try {
            //创建连接
            conn = JDBCUntils.getConnection();
            //获取编译通道
            stmt = conn.prepareStatement("delete from book1 where id=?");
            System.out.println("请输入图书编号");
            int id = Integer.parseInt(sc.nextLine());
            //给？赋值
            stmt.setInt(1,id);
            //执行SQL语句
            System.out.println("是否删除图书 y/n");
            String mgr = sc.nextLine();
            if ("y".equalsIgnoreCase(mgr)){
                int num = stmt.executeUpdate();
                if (num>0){
                    System.out.println("删除成功");
                }else {
                    System.out.println("删除失败");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            JDBCUntils.CloesAll(null,stmt,conn);
        }
        
    }

    public static void main(String[] args) {
login();
    }
}
